<?php
namespace app\com\simtoon\base;

use Yii;
use yii\db\ActiveRecord;
use yii\web\Linkable;
use yii\base\Exception;
use app\com\simtoon\log\LogUtils;
/**
 * BaseCommand基类，处理数据库相关操作以及事物
 */
class BaseCommand extends ActiveRecord implements Linkable
{
	
	public function getLinks()
	{
	
	}
	/**
	 * 数据库连接
	 */
	protected  $connection;

	public function __construct() {
		try{
			$this->connection = Yii::$app->db;
		}catch(Exception $e) {
			$this->echoDBErrorCode($e->getMessage());
		}
	}

	/**
	 * 获取数据库的连接
	 * @return DbConnection
	 */
	private function getConnection() {
		return $this->connection;
	}

	
	/**
	 * 获得数据库执行Command
	 * @param string $sql
	 */
	public function getCommand($sql = null)
	{
		return $this->connection->createCommand($sql);
	}
	
	
	
	/**
	 * 执行insert、update、delete语句
	 * @param string $sql  SQL语句
	 * @return integer 影响的记录数
	 */
	public function executeSql($sql)
	{
		$transaction = $this->connection->beginTransaction();
		try
		{
			$rowsAffected = $this->getCommand($sql)->execute();
		    $transaction->commit();
		    return $rowsAffected;
		}
		catch(Exception $e)
		{
		    $transaction->rollBack();
		    $this->echoDBErrorCode($e->getMessage());
		    return -1;
		}
	}
	
	
	/**
	 * 执行insert语句，如：
	 * insert('user',['name'=>'Tom','age'=>30])
	 * 
	 * 
	 * @param string $table 表名
	 * @param array  $columns 要插入的字段=>值对应。如：array('name' => 'Sam','age' => 30)
	 */
	public function execInsert($table, $columns)
	{
		$transaction = $this->connection->beginTransaction();
		try
		{
			$rowsAffected = $this->getCommand()->insert($table, $columns)->execute();
			$transaction->commit();
			return $rowsAffected;
		}
		catch(Exception $e)
		{
			$transaction->rollBack();
			$this->echoDBErrorCode($e->getMessage());
			return -1;
		}
	}
	
	/**
	 * 批量执行insert，如：
	 * batchInsert('user',['name','age'],[['Tom',20],['Jone',30]])
	 * 
	 * @param string $table 表名 
	 * @param array $columns 字段名 ，如：array('name','age')
	 * @param array $rows  插入的值，如：array(array('Tom',20),array('Jone',30))
	 */
	public function execBatchInsert($table, array $columns, array $rows)
	{
		$transaction = $this->connection->beginTransaction();
		try
		{
			$rowsAffected = $this->getCommand()->batchInsert($table, $columns, $rows)->execute();
			$transaction->commit();
			return $rowsAffected;
		}
		catch(Exception $e)
		{
			$transaction->rollBack();
			$this->echoDBErrorCode($e->getMessage());
			return -1;
		}
	}
	
	
	/**
	 * 执行update语句
	 * @param string $table 表名
	 * @param array  $columns 要更新的字段=>值对应。如：array('name' => 'Sam','age' => 30)
	 * @param string|array  $condition where条件。如：'age>30' 或者     array('and','age>30', array('like','name','a'))
	 * @param array  $params 绑定的参数
	 */
	public function execUpdate($_table, $_columns, $_condition = '', $_params = [])
	{
		$transaction = $this->connection->beginTransaction();
		try
		{
			$rowsAffected = $this->getCommand()->update($_table, $_columns, $_condition)->execute();
			$transaction->commit();
			return $rowsAffected;
		}
		catch(Exception $e)
		{
			$transaction->rollBack();
			$this->echoDBErrorCode($e->getMessage());
			return -1;
		}
	}
	
	/**
	 * 执行delete 语句
	 * @param string $_table 表名
	 * @param string|array  $_condition  条件
	 */
	public function execDelete($_table, $_condition = '')
	{
		$transaction = $this->connection->beginTransaction();
		try
		{
			$rowsAffected = $this->getCommand()->delete($_table, $_condition)->execute();
			$transaction->commit();
			return $rowsAffected;
		}
		catch(Exception $e)
		{
			$transaction->rollBack();
			$this->echoDBErrorCode($e->getMessage());
			return -1;
		}
	}
	
	/**
	 * 执行绑定参数SQL语句
	 * @param string $sql   sql语句
	 * @param array $params 查询参数数组，格式如下：array(参数名 => array(参数值,参数类型))
	 *                                 array(":a" => array("1","int"),":b" => array("2","string"))
	 * @return integer 影响的记录数
	 */
	public function executeByParams($sql, array $params)
	{
		if(!is_array($params))
		{
			return -1;
		}
		$transaction = $this->connection->beginTransaction();
		try 
		{
			$command = $this->getCommand($sql);
			if (isset($params) && is_array($params)) 
			{
				foreach ($params as $key => $val)
				{
					$command->bindParam($key, $val[0], self::getSqlType($val[1]));
				}
			}
			$rowsAffected = $command->execute();
			$transaction->commit();
			return $rowsAffected;
		}
		catch (Exception $e)
		{
			$transaction->rollBack();
			$this->echoDBErrorCode($e->getMessage());
			return -1;
		}
	
	}
	
	/**
	 * 批量执行insert、update、delete语句
	 * @param array $sql_arr  SQL语句数组 array("0"=>sql语句)
	 * @param number $batchSize  批量更新数量, 默认100
	 * @return integer 影响的记录数
	 */
	public function executeSqlBatch(array $sql_arr, $batchSize = 100)
	{
		if(!is_array($sql_arr))
		{
			return -1;
		}
		$transaction = $this->connection->beginTransaction();
		$count = 0;
		try
		{
			$num = 0;
			for($i =0; $i < count($sql_arr); $i++)
            {
                $num = $num + $this->getCommand($sql_arr[$i])->execute();
                
                $count = $i % $batchSize;
                if ($i != 0 && $count == 0)
                {
                    $transaction->commit();
                }
            }
            if ($count > 0)
            {
                $transaction->commit();
            }
            return $num;
		}
		catch (Exception $e)
		{
			$transaction->rollBack();
			$this->echoDBErrorCode($e->getMessage());
			return -1;
		}
	}















    /**
     * 执行insert、update、delete语句
     * @param string $sql  SQL语句
     * @return integer 影响的记录数
     */
    public function executeSqlWithoutTrans($sql)
    {
        $rowsAffected = $this->getCommand($sql)->execute();
        return $rowsAffected;
    }


    /**
     * 执行insert语句，如：
     * insert('user',['name'=>'Tom','age'=>30])
     *
     *
     * @param string $table 表名
     * @param array  $columns 要插入的字段=>值对应。如：array('name' => 'Sam','age' => 30)
     */
    public function execInsertWithoutTrans($table, $columns)
    {
        $rowsAffected = $this->getCommand()->insert($table, $columns)->execute();
        return $rowsAffected;
    }

    /**
     * 批量执行insert，如：
     * batchInsert('user',['name','age'],[['Tom',20],['Jone',30]])
     *
     * @param string $table 表名
     * @param array $columns 字段名 ，如：array('name','age')
     * @param array $rows  插入的值，如：array(array('Tom',20),array('Jone',30))
     */
    public function execBatchInsertWithoutTrans($table, array $columns, array $rows)
    {
        $rowsAffected = $this->getCommand()->batchInsert($table, $columns, $rows)->execute();
        return $rowsAffected;
    }


    /**
     * 执行update语句
     * @param string $table 表名
     * @param array  $columns 要更新的字段=>值对应。如：array('name' => 'Sam','age' => 30)
     * @param string|array  $condition where条件。如：'age>30' 或者     array('and','age>30', array('like','name','a'))
     * @param array  $params 绑定的参数
     */
    public function execUpdateWithoutTrans($_table, $_columns, $_condition = '', $_params = [])
    {
        $rowsAffected = $this->getCommand()->update($_table, $_columns, $_condition)->execute();
        return $rowsAffected;
    }

    /**
     * 执行delete 语句
     * @param string $_table 表名
     * @param string|array  $_condition  条件
     */
    public function execDeleteWithoutTrans($_table, $_condition = '')
    {
        $rowsAffected = $this->getCommand()->delete($_table, $_condition)->execute();
        return $rowsAffected;
    }

    /**
     * 执行绑定参数SQL语句
     * @param string $sql   sql语句
     * @param array $params 查询参数数组，格式如下：array(参数名 => array(参数值,参数类型))
     *                                 array(":a" => array("1","int"),":b" => array("2","string"))
     * @return integer 影响的记录数
     */
    public function executeByParamsWithoutTrans($sql, array $params)
    {
        if(!is_array($params))
        {
            return -1;
        }
        $command = $this->getCommand($sql);
        if (isset($params) && is_array($params))
        {
            foreach ($params as $key => $val)
            {
                $command->bindParam($key, $val[0], self::getSqlType($val[1]));
            }
        }
        $rowsAffected = $command->execute();
        return $rowsAffected;

    }

    /**
     * 批量执行insert、update、delete语句
     * @param array $sql_arr  SQL语句数组 array("0"=>sql语句)
     * @param number $batchSize  批量更新数量, 默认100
     * @return integer 影响的记录数
     */
    public function executeSqlBatchWithoutTrans(array $sql_arr, $batchSize = 100)
    {
        if(!is_array($sql_arr))
        {
            return -1;
        }
        $num = 0;
        for($i = 0; $i < count($sql_arr); $i++)
        {
            $num = $num + $this->getCommand($sql_arr[$i])->execute();

        }
        return $num;
    }


	
	/**
	 * 查询单条记录
	 * @param String $sql  SQL语句
	 * @param integer $fetchMode  
	 * @return 
	 */
	public function queryOne($sql, $fetchMode = null)
	{
		try 
		{
			return $this->getCommand($sql)->queryOne($fetchMode);
		}
		catch (Exception $e)
		{
			$this->echoDBErrorCode($e->getMessage());
			return array();
		}
	}
	
	/**
	 * 查询多条记录
	 * @param String $sql  SQL语句
	 * @param integer $fetchMode
	 * @return
	 */
	public function queryAll($sql, $fetchMode = null)
	{
		try 
		{
			return $this->getCommand($sql)->queryAll($fetchMode);
		}
		catch (Exception $e)
		{
			$this->echoDBErrorCode($e->getMessage());
			return array();
		}
		
	}
	/**
	 * 绑定参数进行查询
	 * @param string $sql   sql语句
	 * @param array $params 查询参数数组，格式如下：array(参数名 => array(参数值,参数类型))
	 *                                 array(":a" => array("1","int"),":b" => array("2","string"))
	 * @param integer $fetchMode
	 * @return 
	 */
	public function queryAllByParams($sql, array $params, $fetchMode = null)
	{
		if(!is_array($params))
		{
			return -1;
		}
		try 
		{
			$command = $this->getCommand($sql);
			if (isset($params) && is_array($params)) 
			{
				foreach ($params as $key => $val)
				{
					$command->bindParam($key, $val[0], self::getSqlType($val[1]));
				}
				
			}
			return $command->queryAll($fetchMode);
		}
		catch (Exception $e)
		{
			$this->echoDBErrorCode($e->getMessage());
			return array();
		}
	
	}
	
	/**
	 * 查询列
	 * @param String $sql  SQL语句
	 * @return
	 */
	public function queryColumn($sql)
	{
		try 
		{
			return $this->getCommand($sql)->queryColumn();
		}
		catch (Exception $e)
		{
			$this->echoDBErrorCode($e->getMessage());
			return array();
		}
	
	}
	
	public function getSqlType($type)
	{
		static $typeMap = [
			// php type => PDO type
			'boolean' => \PDO::PARAM_BOOL,
			'bol' => \PDO::PARAM_BOOL,
			'integer' => \PDO::PARAM_INT,
			'int' => \PDO::PARAM_INT,
			'string' => \PDO::PARAM_STR,
			'resource' => \PDO::PARAM_LOB,
			'clob' => \PDO::PARAM_LOB,
			'NULL' => \PDO::PARAM_NULL,
		];
		
		return isset($typeMap[$type]) ? $typeMap[$type] : null;
	}

//用户多条SQL放于同一事务中
    public function execMethod($method_name, $_condition = '')
    {
        $transaction = $this->connection->beginTransaction();
        try
        {
            $res = $this->$method_name($_condition);
            $transaction->commit();
            return $res;
        }
        catch(Exception $e)
        {
            $transaction->rollBack();
            $this->echoDBErrorCode($e->getMessage());
            return -1;
        }
    }


	
	/**
	 * 打印数据库错误信息
	 * @param string $errMessage 错误信息
	 */
	public function echoDBErrorCode($errMessage = null){
		if(!$errMessage) $errMessage = "DataBase connect/execute fail!";
		LogUtils::error("ErrorInfo={" . $errMessage . "}", __METHOD__);
	}
	
}

?>